/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import tuitionmanagement.entity.SubOrderEntity;
import tuitionmanagement.util.DBUtil;

/**
 *
 * @author XUAN
 */
public class SubOrderDao {

    private Connection con;
    private ResultSet rs;
    private PreparedStatement pst;
    private String lastError;
    private final String sql_selectSubOrder = "select * from SubOrder";
    private final String sql_selectOneSubOrder = "select * from SubOrder where StudentId =?";
    private final String sql_selectSubOrderByName = "select SubOrderId,Description,RateId,FeeLevelId,[Money],FeeDate from SubOrder join Student on SubOrder.StudentId = Student.StudentId where StudentName = ?";
    private final String sql_insertSubOrder = "insert into SubOrder(StudentId,Description,[Money],FeeLevelId,FeeDate,RateId) values(?,?,?,?,?,?)";
    private final String sql_updateSubOrder = "update SubOrder set StudentId = ?, Description = ?,Money=?,FeeLevelId=?,FeeDate=?, RateId=? where SubOrderId =?";
    private final String sql_deleteSubOrder = "delete from SubOrder where SubOrderId =?";

    public ArrayList<SubOrderEntity> getSubOrderOneStudent(String search) {
        ArrayList<SubOrderEntity> list = new ArrayList<SubOrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectOneSubOrder);
            pst.setString(1, search);

            rs = pst.executeQuery();

            while (rs.next()) {
                SubOrderEntity sub = new SubOrderEntity();
                sub.setSubOrderId(rs.getInt("SubOrderId"));
                sub.setStudentId(rs.getInt("StudentId"));
                sub.setDescription(rs.getString("Description"));
                sub.setRateId(rs.getInt("RateId"));
                sub.setFeeLevelId(rs.getInt("FeeLevelId"));
                sub.setMoney(rs.getFloat("Money"));
                sub.setFeeDate(rs.getDate(("FeeDate")));

                list.add(sub);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public ArrayList<SubOrderEntity> readByAll() {

        ArrayList<SubOrderEntity> list = new ArrayList<SubOrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectSubOrder);
            rs = pst.executeQuery();
            while (rs.next()) {
                SubOrderEntity b = new SubOrderEntity();

                b.setSubOrderId(rs.getInt("SubOrderId"));
                b.setStudentId(rs.getInt("StudentId"));
                b.setDescription(rs.getString("Description"));
                b.setRateId(rs.getInt("RateId"));
                b.setFeeLevelId(rs.getInt("FeeLevelId"));
                b.setMoney(rs.getFloat("[Money]"));
                b.setFeeDate(rs.getDate(("FeeDate")));

                list.add(b);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;

    }

    public ArrayList<SubOrderEntity> getSubOrderByName(String last_name) {
        ArrayList<SubOrderEntity> list = new ArrayList<SubOrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectSubOrderByName);
            pst.setString(1, last_name);
            rs = pst.executeQuery();
            while (rs.next()) {
                SubOrderEntity b = new SubOrderEntity();

                b.setSubOrderId(rs.getInt("SubOrderId"));
                b.setStudentId(rs.getInt("StudentId"));
                b.setDescription(rs.getString("Descripton"));
                b.setMoney(rs.getFloat("Money"));
                b.setFeeLevelId(rs.getInt("FeeLevelId"));
                b.setFeeDate(rs.getDate(("FeeDate")));
                b.setRateId(rs.getInt("RateId"));

                list.add(b);

            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public boolean addSubOrder(SubOrderEntity b) {
        if (!b.validate()) {
            setLastError(b.getLastError());
            return false;
        }
        con = DBUtil.getConn();
        try {

            pst = con.prepareStatement(sql_insertSubOrder);
//            pst.setInt(1, b.getSubOrderId());
            pst.setInt(1, b.getStudentId());
            pst.setString(2, b.getDescription());
            pst.setFloat(3, b.getMoney());
            pst.setInt(4, b.getFeeLevelId());
            pst.setDate(5, b.getFeeDate());
            pst.setInt(6, b.getRateId());


            if (pst.executeUpdate() > 0) {
                setLastError("Add Employee's information successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }

        return false;
    }

    public boolean update(SubOrderEntity b) {
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_updateSubOrder, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, b.getStudentId());
            rs = pst.executeQuery();
            rs.first();
            pst.setInt(1, b.getStudentId());
            pst.setString(2, b.getDescription());
            pst.setInt(6, b.getRateId());
            pst.setInt(4, b.getFeeLevelId());
            pst.setFloat(3, b.getMoney());
            pst.setDate(5, b.getFeeDate());
            pst.setInt(7, b.getSubOrderId());
            rs.updateRow();
            con.close();
            setLastError("Update Employee successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public boolean delete(int subOrderId) {
        Connection con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_deleteSubOrder, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, subOrderId);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete Employee successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
